﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Text.RegularExpressions;

using System.Data;
using System.Data.SqlClient;
using System.Configuration;


namespace syc.Jidong
{
    public partial class 设备管理 : System.Web.UI.Page
    {
        private string sql = "select ID,编码,名称,型号,厂家,总成本,出厂日期,投产日期,附件数,状态,case when 状态=0 then '正常' else '报废' end as 状态描述 from 设备台账";
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                this.SqlDataSource1.SelectCommand = sql + " order by ID desc";
                this.SqlText.Text = this.SqlDataSource1.SelectCommand;
            }
            else
            {
                this.SqlDataSource1.SelectCommand = this.SqlText.Text;
            }
            this.Panel_Msg.Visible = false;
        }

        protected void GridView1_DataBound(object sender, EventArgs e)
        {
            this.GridView1.SelectedIndex = -1;
            this.Button_Edit.Enabled = false;
            this.Button_Annex.Enabled = false;
            this.Button_Delete.Enabled = false;
            this.Button_Tran.Enabled = false;
            this.Button_Discard.Enabled = false;

            this.TextBox_Pager.Text = (this.GridView1.PageIndex + 1).ToString();
            this.Label_Pages.Text = " 共" + this.GridView1.PageCount.ToString() + "页 ";

            this.Button_Pager_First.CssClass = "pageturn-first-b";
            this.Button_Pager_First.Enabled = true;
            this.Button_Pager_Prev.CssClass = "pageturn-prev-b";
            this.Button_Pager_Prev.Enabled = true;
            this.Button_Pager_Next.CssClass = "pageturn-next-b";
            this.Button_Pager_Next.Enabled = true;
            this.Button_Pager_Last.CssClass = "pageturn-last-b";
            this.Button_Pager_Last.Enabled = true;
            if (this.GridView1.PageIndex == 0)
            {
                this.Button_Pager_Prev.CssClass = "pageturn-prev-g";
                this.Button_Pager_Prev.Enabled = false;
            }
            if (this.GridView1.PageIndex < 2)
            {
                this.Button_Pager_First.CssClass = "pageturn-first-g";
                this.Button_Pager_First.Enabled = false;
            }
            if (this.GridView1.PageIndex == this.GridView1.PageCount - 1 || this.GridView1.PageCount == 0)
            {
                this.Button_Pager_Next.CssClass = "pageturn-next-g";
                this.Button_Pager_Next.Enabled = false;

            }
            if (this.GridView1.PageIndex > this.GridView1.PageCount - 3)
            {
                this.Button_Pager_Last.CssClass = "pageturn-last-g";
                this.Button_Pager_Last.Enabled = false;
            }

        }
        protected void GridView1_PageIndexChanged(object sender, EventArgs e)
        {
            this.GridView1_DataBound(null, null);
        }
        protected void Pager_Click(object sender, EventArgs e)
        {
            if (sender.GetType().ToString() == "System.Web.UI.WebControls.Button")
            {
                switch (((Button)sender).CommandName)
                {
                    case "--":
                        this.GridView1.PageIndex = 0;
                        break;
                    case "-":
                        if (this.GridView1.PageIndex > 0) this.GridView1.PageIndex--;
                        break;
                    case "+":
                        if (this.GridView1.PageIndex < this.GridView1.PageCount - 1) this.GridView1.PageIndex++;
                        break;
                    case "++":
                        this.GridView1.PageIndex = this.GridView1.PageCount - 1;
                        break;
                }
            }
            else
            {

                int p;
                if (int.TryParse(TextBox_Pager.Text, out p))
                {
                    if (p >= 1 && p <= this.GridView1.PageCount)
                    {
                        this.GridView1.PageIndex = p - 1;
                    }
                }
                TextBox_Pager.Text = (this.GridView1.PageIndex + 1).ToString();
            }

        }
        protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e)
        {
            Label_Total.Text = "共查询到 " + e.AffectedRows + " 项记录";
        }
        protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.SelectedID.Text = this.GridView1.SelectedDataKey[0].ToString();
            this.Button_Edit.Enabled = true;
            this.Button_Annex.Enabled = true;
            this.Button_Delete.Enabled = true;
            this.Button_Tran.Enabled = true;
            //if ((byte)this.GridView1.SelectedDataKey[1] == 0)
            //{
            //    this.Button_Discard.Enabled = true;
            //}
            //else
            //{
            //    this.Button_Discard.Enabled = false;
            //}
            this.Button_Discard.Enabled = true;
        }

        protected void Button_Cancel_Click(object sender, EventArgs e)
        {
            this.Panel2.Visible = false;
        }
        private string GetState()
        {
            string state = "";
            this.BianHao.Text = Regex.Replace(this.BianHao.Text, "[^0-9]+", "", RegexOptions.None);
            if (this.BianHao.Text != "" && this.BianHao.Text != "000000")
            {
                if(this.BianHao.Text.StartsWith("000000"))
                {
                    state += " substring(编码,7,12) like '" + this.BianHao.Text.Substring(6) + "%'";
                }
                else{
                    
                    state += " 编码 like '" + this.BianHao.Text + "%'";
                }
            }
            this.MingCheng.Text = Regex.Replace(this.MingCheng.Text, "[_\\[%\\?\\']+", "", RegexOptions.None);
            if (this.MingCheng.Text != "")
            {
                state += " and 名称 like '%" + this.MingCheng.Text + "%'";
            }

            this.XingHao.Text = Regex.Replace(this.XingHao.Text, "[_\\[%\\?\\']+", "", RegexOptions.None);
            if (this.XingHao.Text != "")
            {
                state += " and 型号 like '%" + this.XingHao.Text + "%'";
            }
            this.ChangJia.Text = Regex.Replace(this.ChangJia.Text, "[_\\[%\\?\\']+", "", RegexOptions.None);
            if (this.ChangJia.Text != "")
            {
                state += " and 厂家 like '%" + this.ChangJia.Text + "%'";
            }

            if (!this.BaoFei.Checked)
            {
                state += " and 状态=0";
            }
            DateTime d;
            if (DateTime.TryParse(this.Date1.Text, out d))
            {
                state += " and 投产日期>='" + d.ToShortDateString() + "'";
            }
            if (DateTime.TryParse(this.Date2.Text, out d))
            {
                state += " and 投产日期<='" + d.ToShortDateString() + "'";
            }
            if (state.StartsWith(" and"))
            {
                state = state.Substring(4);
            }
            if (state != "")
            {
                state = " where " + state;
            }
            return state;
        }
        protected void Button_Query_Click(object sender, EventArgs e)
        {
            string state = this.GetState();
            this.SqlText.Text = sql + state +" order by ID desc";
            this.SqlDataSource1.SelectCommand = "";

            this.SqlDataSource1.SelectCommand = this.SqlText.Text;
        }

        protected void Button_Reset_Click(object sender, EventArgs e)
        {
            this.BianHao.Text = "";
            this.MingCheng.Text = "";
            this.XingHao.Text = "";
            this.ChangJia.Text = "";
            this.Date1.Text = "";
            this.Date2.Text = "";
        }

        protected void Button_Delete_Click(object sender, EventArgs e)
        {
            this.SqlDataSource1.DeleteCommand = "delete from 设备台账 where ID="+this.SelectedID.Text;
            this.SqlDataSource1.Delete();
        }

        protected void Button_TaiZhang_Click(object sender, EventArgs e)
        {
            string part1 =
            @"<html xmlns:o='urn:schemas-microsoft-com:office:office'
xmlns:x='urn:schemas-microsoft-com:office:excel'
xmlns='http://www.w3.org/TR/REC-html40'>
<head>
<meta http-equiv=Content-Type content='text/html; charset=utf-8'>
<style>
<!--table
@page
{mso-header-data:;
mso-footer-data:;
margin:.79in .31in .79in .31in;
mso-header-margin:.51in;
mso-footer-margin:.51in;
mso-page-orientation:landscape;
mso-horizontal-page-align:center;
}
-->
</style>
<!--[if gte mso 9]><xml>
 <x:ExcelWorkbook>
  <x:ExcelWorksheets>
   <x:ExcelWorksheet>
    <x:Name>设备台账</x:Name>
    <x:WorksheetOptions>
     <x:DefaultRowHeight>285</x:DefaultRowHeight>
     <x:Print>
      <x:ValidPrinterInfo/>
      <x:Scale>100</x:Scale>
      <x:PaperSizeIndex>9</x:PaperSizeIndex>
      <x:HorizontalResolution>200</x:HorizontalResolution>
      <x:VerticalResolution>200</x:VerticalResolution>
     </x:Print>
     <x:Selected/>
     <x:Panes>
      <x:Pane>
       <x:Number>3</x:Number>
       <x:ActiveRow>0</x:ActiveRow>
       <x:ActiveCol>0</x:ActiveCol>
      </x:Pane>
     </x:Panes>
    </x:WorksheetOptions>
   </x:ExcelWorksheet>
 </x:ExcelWorkbook>
</xml><![endif]-->
</head>
<body>";
            string part2 =
            @"<style>
tr{height:14pt;}
td{border:.5pt solid #000000;font-size:10pt;text-align:center;}
td.nb{border:none;}
</style>
<table border=0 cellpadding=0 style=border-collapse:collapse>
<tr>
<td colspan=10 class=nb style=font-size:14pt;><b>设　备　台　账</b></td>
</tr>
<tr>
<td colspan=10 class=nb style=text-align:right;>编号：SYC/ZJ-JD-01&nbsp;</td>
</tr>
<tr>
<td style=width:24pt;>序号</td>
<td style=width:120pt;>设备名称</td>
<td style=width:100pt;>型号及规格</td>
<td style=width:60pt;>出厂日期</td>
<td style=width:90pt;>用途或地址</td>
<td style=width:30pt;>效用<br>年限</td>
<td style=width:60pt;>投产日期</td>
<td style=width:60pt;>转出日期</td>
<td style=width:60pt;>接收单位</td>
<td style=width:100pt;>备注</td>
</tr>";
            string state = this.GetState();

            Response.Charset = "utf-8";
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            Response.ContentType = "application/ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=\"" + Server.UrlEncode("设备台账") + ".xls\"");

            Response.Write(part1);
            Response.Write(part2);


            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            Cmd.CommandText = "select 名称,isnull(型号,'') as 型号,出厂日期,isnull(安装地点,'') as 安装地点,折旧年限,投产日期,isnull(备注,'') as 备注 from 设备台账 " + state;

            Cmd.CommandType = CommandType.Text;
            SqlDataReader Reader = Cmd.ExecuteReader();
            int n = 1;
            while (Reader.Read())
            {
                Response.Write("<tr><td>"+n+"</td>");
                Response.Write("<td style=text-align:left>" + (string)Reader["名称"] + "</td>");
                Response.Write("<td style=text-align:left>" + (string)Reader["型号"] + "</td>");
                if (Convert.IsDBNull(Reader["出厂日期"]))
                {
                    Response.Write("<td></td>");
                }
                else
                {
                    Response.Write("<td>" + ((DateTime)Reader["出厂日期"]).ToString("yyyyMMdd") + "</td>");
                }

                Response.Write("<td style=text-align:left>" + (string)Reader["安装地点"] + "</td>");
                if (Convert.IsDBNull(Reader["折旧年限"]))
                {
                    Response.Write("<td></td>");
                }
                else
                {
                    Response.Write("<td>" + ((short)Reader["折旧年限"]).ToString() + "</td>");
                }
                if (Convert.IsDBNull(Reader["投产日期"]))
                {
                    Response.Write("<td></td>");
                }
                else
                {
                    Response.Write("<td>" + ((DateTime)Reader["投产日期"]).ToString("yyyyMMdd") + "</td>");
                }
                Response.Write("<td></td>");
                Response.Write("<td></td>");
                Response.Write("<td style=text-align:left>" + (string)Reader["备注"] + "</td></tr>");
                n++;
            }

            Reader.Close();
            Conn.Close();
            Reader.Dispose();
            Cmd.Dispose();
            Conn.Dispose();

            Response.End();
        }

        protected void Button_MingXi_Click(object sender, EventArgs e)
        {

            string part1 =
            @"<html xmlns:o='urn:schemas-microsoft-com:office:office'
xmlns:x='urn:schemas-microsoft-com:office:excel'
xmlns='http://www.w3.org/TR/REC-html40'>
<head>
<meta http-equiv=Content-Type content='text/html; charset=utf-8'>
<style>
<!--table
@page
{mso-header-data:;
mso-footer-data:;
margin:.39in .12in .39in .12in;
mso-header-margin:.51in;
mso-footer-margin:.51in;
mso-page-orientation:landscape;
mso-horizontal-page-align:center;
}
-->
</style>
<!--[if gte mso 9]><xml>
 <x:ExcelWorkbook>
  <x:ExcelWorksheets>
   <x:ExcelWorksheet>
    <x:Name>固定资产明细</x:Name>
    <x:WorksheetOptions>
     <x:DefaultRowHeight>285</x:DefaultRowHeight>
     <x:FitToPage/>
     <x:FitToPage/>
     <x:Print>
      <x:ValidPrinterInfo/>
      <x:Scale>100</x:Scale>
      <x:PaperSizeIndex>9</x:PaperSizeIndex>
      <x:HorizontalResolution>200</x:HorizontalResolution>
      <x:VerticalResolution>200</x:VerticalResolution>
     </x:Print>
     <x:Selected/>
     <x:Panes>
      <x:Pane>
       <x:Number>3</x:Number>
       <x:ActiveRow>0</x:ActiveRow>
       <x:ActiveCol>0</x:ActiveCol>
      </x:Pane>
     </x:Panes>
    </x:WorksheetOptions>
   </x:ExcelWorksheet>
 </x:ExcelWorkbook>
</xml><![endif]-->
</head>
<body>";
            string part2 =
            @"<style>
td{border:.5pt solid #000000;font-size:8pt;text-align:center;mso-number-format:\@; }
td.nb{border:none;}
</style>
<table border=0 cellpadding=0 style=border-collapse:collapse;>
<tr>
<td colspan=14 class=nb style=font-size:14pt;><b>设　备　台　账</b>（固定资产明细）</td>
</tr>
<tr>
<td colspan=14 class=nb style=text-align:right;>编号：SYC/ZJ-JD-05&nbsp;</td>
</tr>
<tr style=height:20pt>
<td style=width:86pt;>资产编号</td>
<td style=width:90pt;>设备名称</td>
<td style=width:80pt;>型号规格</td>
<td style=width:90pt;>制造厂</td>
<td style=width:40pt;>出厂日期</td>
<td style=width:40pt;>出厂编号</td>
<td style=width:50pt;>原值</td>
<td style=width:24pt;>附件
个数</td>
<td style=width:24pt;>功率
(KW)</td>
<td style=width:30pt;>大小
(m<sup>2</sup>)
(T)</td>
<td style=width:60pt;>安装地或用途</td>
<td style=width:24pt;>效用
年限</td>
<td style=width:40pt;>投产日期</td>
<td style=width:40pt;>备注</td>
</tr>";
            string state = this.GetState();


            Response.Charset = "utf-8";
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            Response.ContentType = "application/ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=\"" + Server.UrlEncode("固定资产明细") + ".xls\"");

            Response.Write(part1);
            Response.Write(part2);


            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            Cmd.CommandText =
@"select 编码
,名称
,isnull(型号,'') as 型号
,isnull(厂家,'') as 厂家
,出厂日期
,isnull(出厂编号,'') as 出厂编号
,总成本
,附件数
,isnull(功率,-1) as 功率
,isnull(大小,'') as 大小
,isnull(安装地点,'') as 安装地点
,isnull(折旧年限,-1) as 折旧年限
,投产日期
,isnull(备注,'') as 备注
from 设备台账 " + state;

            Cmd.CommandType = CommandType.Text;
            SqlDataReader Reader = Cmd.ExecuteReader();
            int n = 1;
            decimal zcb = 0;
            while (Reader.Read())
            {
                Response.Write("<tr><td>" + (string)Reader["编码"] + "</td>");
                Response.Write("<td style=text-align:left>" + (string)Reader["名称"] + "</td>");
                Response.Write("<td style=text-align:left>" + (string)Reader["型号"] + "</td>");
                Response.Write("<td style=text-align:left>" + (string)Reader["厂家"] + "</td>");
                if (Convert.IsDBNull(Reader["出厂日期"]))
                {
                    Response.Write("<td></td>");
                }
                else
                {
                    Response.Write("<td>" + ((DateTime)Reader["出厂日期"]).ToString("yyyyMMdd") + "</td>");
                }
                Response.Write("<td style=text-align:left>" + (string)Reader["出厂编号"] + "</td>");
                zcb += (decimal)Reader["总成本"];
                Response.Write("<td style=text-align:right>" + ((decimal)Reader["总成本"]).ToString("0.00") + "</td>");
                if ((int)Reader["附件数"] == 0)
                {
                    Response.Write("<td></td>");
                }
                else
                {
                    Response.Write("<td>" + ((int)Reader["附件数"]).ToString() + "</td>");
                }

                if ((double)Reader["功率"]==-1)
                {
                    Response.Write("<td></td>");
                }
                else
                {
                    Response.Write("<td style=text-align:right>" + ((double)Reader["功率"]).ToString() + "</td>");
                }
                Response.Write("<td style=text-align:right>" + (string)Reader["大小"] + "</td>");
                Response.Write("<td style=text-align:left>" + (string)Reader["安装地点"] + "</td>");
                if ((short)Reader["折旧年限"]==-1)
                {
                    Response.Write("<td></td>");
                }
                else
                {
                    Response.Write("<td>" + ((short)Reader["折旧年限"]).ToString() + "</td>");
                }
                if (Convert.IsDBNull(Reader["投产日期"]))
                {
                    Response.Write("<td></td>");
                }
                else
                {
                    Response.Write("<td>" + ((DateTime)Reader["投产日期"]).ToString("yyyyMMdd") + "</td>");
                }
                Response.Write("<td style=text-align:left>" + (string)Reader["备注"] + "</td></tr>");
                n++;
            }
            Response.Write("<tr><td class=nb colspan=7 style=text-align:right>" + zcb.ToString("0.00") + "</td><td class=nb colspan=7></td></tr>");
            Reader.Close();
            Conn.Close();
            Reader.Dispose();
            Cmd.Dispose();
            Conn.Dispose();

            Response.End();
        }
    }
}
